﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    /// <summary>
    /// 数据库升级访问层
    /// </summary>
    public class DBUpdateDAL
    {
        #region v1.0

        private const string SQL_CREATE_TABLE_WS_DATABASE_VERSION = @"CREATE TABLE WS_DATABASE_VERSION (DBV_VERSION VARCHAR(10) NOT NULL,DBV_UPDATE_TIME DATETIME NOT NULL,PRIMARY KEY(DBV_VERSION))";

        private const string SQL_CREATE_TABLE_WS_GLOBAL_PARAMS = @"CREATE TABLE WS_GLOBAL_PARAMS (GOP_PARAM_KEY VARCHAR(20) NOT NULL,GOP_PARAM_VALUE VARCHAR(64) NOT NULL,PRIMARY KEY(GOP_PARAM_KEY))";

        private const string SQL_CREATE_TABLE_WS_SEQUENCES = @"CREATE TABLE WS_SEQUENCES (SEQ_NAME VARCHAR(30) NOT NULL,SEQ_MIN_VALUE DECIMAL NOT NULL,SEQ_MAX_VALUE DECIMAL NOT NULL,SEQ_NEXT_NUMBER DECIMAL NOT NULL,SEQ_INCREMENT_BY DECIMAL NOT NULL,SEQ_CYCLE_FLG VARCHAR(1) NOT NULL,PRIMARY KEY(SEQ_NAME))";

        private const string SQL_CREATE_TABLE_WS_MODULE = @"CREATE TABLE WS_MODULE (MOD_MODULE_ID VARCHAR(8) NOT NULL,MOD_MODULE_NAME VARCHAR(30) NOT NULL,MOD_PARENT_MODULE_ID VARCHAR(8) NOT NULL,MOD_SORT_INDEX INTEGER(3) NOT NULL,PRIMARY KEY(MOD_MODULE_ID))";

        private const string SQL_CREATE_TABLE_WS_FUNCTION = @"CREATE TABLE WS_FUNCTION (FUN_FUNCTION_ID VARCHAR(10) NOT NULL,FUN_FUNCTION_NAME VARCHAR(30) NOT NULL,FUN_MODULE_ID VARCHAR(8) NOT NULL,FUN_SORT_INDEX INTEGER(3) NOT NULL,PRIMARY KEY(FUN_FUNCTION_ID),INDEX WS_FUN_NX1(FUN_MODULE_ID))";

        private const string SQL_CREATE_TABLE_WS_ROLE = @"CREATE TABLE WS_ROLE (ROL_ROLE_ID VARCHAR(5) NOT NULL,ROL_ROLE_NAME VARCHAR(30) NOT NULL,ROL_ROLE_LEVEL INTEGER(2) NOT NULL,ROL_RCRE_STAFF_ID VARCHAR(20) NOT NULL,ROL_RCRE_TIME DATETIME NOT NULL,ROL_LCHG_STAFF_ID VARCHAR(20),ROL_LCHG_TIME DATETIME,PRIMARY KEY(ROL_ROLE_ID),INDEX WS_ROL_NX1(ROL_RCRE_STAFF_ID),INDEX WS_ROL_NX2(ROL_LCHG_STAFF_ID))";

        private const string SQL_CREATE_TABLE_WS_ROLE_MODULE = @"CREATE TABLE WS_ROLE_MODULE (ROM_ROLE_ID VARCHAR(5) NOT NULL,ROM_MODULE_ID VARCHAR(8) NOT NULL,ROM_RCRE_STAFF_ID VARCHAR(20) NOT NULL,ROM_RCRE_TIME DATETIME NOT NULL,ROM_LCHG_STAFF_ID VARCHAR(20),ROM_LCHG_TIME DATETIME,PRIMARY KEY(ROM_ROLE_ID,ROM_MODULE_ID),INDEX WS_ROM_NX1(ROM_RCRE_STAFF_ID),INDEX WS_ROM_NX2(ROM_LCHG_STAFF_ID))";

        private const string SQL_CREATE_TABLE_WS_ROLE_FUNCTION = @"CREATE TABLE WS_ROLE_FUNCTION (ROF_ROLE_ID VARCHAR(5) NOT NULL,ROF_FUNCTION_ID VARCHAR(10) NOT NULL,ROF_RCRE_STAFF_ID VARCHAR(20) NOT NULL,ROF_RCRE_TIME DATETIME NOT NULL,ROF_LCHG_STAFF_ID VARCHAR(20),ROF_LCHG_TIME DATETIME,PRIMARY KEY(ROF_ROLE_ID,ROF_FUNCTION_ID),INDEX WS_ROF_NX1(ROF_RCRE_STAFF_ID),INDEX WS_ROF_NX2(ROF_LCHG_STAFF_ID))";

        private const string SQL_CREATE_TABLE_WS_STAFF = @"CREATE TABLE WS_STAFF (STF_STAFF_ID VARCHAR(20) NOT NULL,STF_STAFF_NAME VARCHAR(20),STF_PASSWORD VARCHAR(32) NOT NULL,STF_ROLE_ID VARCHAR(5) NOT NULL,STF_JOIN_DATE DATETIME NOT NULL,STF_DIMISSION_FLG VARCHAR(1) NOT NULL,STF_RCRE_STAFF_ID VARCHAR(20) NOT NULL,STF_RCRE_TIME DATETIME NOT NULL,STF_LCHG_STAFF_ID VARCHAR(20),STF_LCHG_TIME DATETIME,PRIMARY KEY(STF_STAFF_ID),INDEX WS_STF_NX1(STF_RCRE_STAFF_ID),INDEX WS_STF_NX2(STF_LCHG_STAFF_ID),INDEX WS_STF_NX3(STF_ROLE_ID))";

        private const string SQL_CREATE_TABLE_WS_STAFF_ACTIVITIES = @"CREATE TABLE WS_STAFF_ACTIVITIES (SFA_STAFF_ID VARCHAR(20) NOT NULL, SFA_LOGIN_TIME DATETIME NOT NULL,SFA_LOGOUT_TIME DATETIME,SFA_COMPUTER_NAME VARCHAR(50) NOT NULL,SFA_IP_ADDRESS VARCHAR(20) NOT NULL, SFA_MAC_ADDRESS VARCHAR(20) NOT NULL, SFA_SESSION_ID VARCHAR(32) NOT NULL,INDEX WS_SFA_NX1(SFA_STAFF_ID),INDEX WS_SFA_NX2(SFA_STAFF_ID,SFA_COMPUTER_NAME))";

        private const string SQL_CREATE_TABLE_WS_STAFF_LOGIN_STATE = @"CREATE TABLE WS_STAFF_LOGIN_STATE (SLS_STAFF_ID VARCHAR(20) NOT NULL, SLS_LOGIN_TIME DATETIME NOT NULL, SLS_COMPUTER_NAME VARCHAR(50) NOT NULL,SLS_IP_ADDRESS VARCHAR(20) NOT NULL, SLS_MAC_ADDRESS VARCHAR(20) NOT NULL, SLS_SESSION_ID VARCHAR(32) NOT NULL,INDEX WS_SLS_NX1(SLS_STAFF_ID),INDEX WS_SLS_NX2(SLS_SESSION_ID))";

        private const string SQL_CREATE_TABLE_WS_STAFF_LOG = @"CREATE TABLE WS_STAFF_LOG (SFL_LOG_ID VARCHAR(20) NOT NULL,SFL_STAFF_ID VARCHAR(20) NOT NULL,SFL_OPERATE_TIME DATETIME NOT NULL,SFL_MODULE_ID VARCHAR(8),SFL_REMARK MEDIUMTEXT NOT NULL,PRIMARY KEY (SFL_LOG_ID),INDEX WS_SFL_NX1(SFL_STAFF_ID),INDEX WS_SFL_NX2(SFL_MODULE_ID))";

        private const string SQL_CREATE_TABLE_WS_CITY = @"CREATE TABLE WS_CITY (CIT_CITY_ID VARCHAR(10) NOT NULL,CIT_CITY_NAME VARCHAR(30) NOT NULL,CIT_PARENT_CITY_ID VARCHAR(10) NOT NULL,CIT_SORT_INDEX INTEGER(3) NOT NULL,CIT_AIRPORT_NAME VARCHAR(50),CIT_AIRPORT_CODE_3 VARCHAR(5),CIT_AIRPORT_CODE_4 VARCHAR(5),CIT_CHINESE_SPELL VARCHAR(50),CIT_ENGLISH_NAME VARCHAR(50),CIT_RCRE_STAFF_ID VARCHAR(20) NOT NULL,CIT_RCRE_TIME DATETIME NOT NULL,CIT_LCHG_STAFF_ID VARCHAR(20),CIT_LCHG_TIME DATETIME,PRIMARY KEY(CIT_CITY_ID),INDEX WS_CIT_NX1(CIT_RCRE_STAFF_ID),INDEX WS_CIT_NX2(CIT_LCHG_STAFF_ID),INDEX WS_CIT_NX3(CIT_PARENT_CITY_ID))";

        private const string SQL_CREATE_TABLE_WS_AIRLINE = @"CREATE TABLE WS_AIRLINE (ALI_AIRLINE_ID VARCHAR(10) NOT NULL,ALI_AIRLINE_NAME VARCHAR(50) NOT NULL,ALI_AIRLINE_CODE_3 VARCHAR(5) NOT NULL,ALI_SORT_INDEX INTEGER(3) NOT NULL,ALI_ENGLISH_NAME VARCHAR(50),ALI_RCRE_STAFF_ID VARCHAR(20) NOT NULL,ALI_RCRE_TIME DATETIME NOT NULL,ALI_LCHG_STAFF_ID VARCHAR(20),ALI_LCHG_TIME DATETIME,PRIMARY KEY (ALI_AIRLINE_ID),INDEX WS_ALI_NX1(ALI_RCRE_STAFF_ID),INDEX WS_ALI_NX2(ALI_LCHG_STAFF_ID))";

        private const string SQL_CREATE_TABLE_WS_ID_TYPE = @"CREATE TABLE WS_ID_TYPE (IDT_TYPE_ID VARCHAR(2) NOT NULL,IDT_TYPE_NAME VARCHAR(20) NOT NULL,IDT_ENABLED VARCHAR(1) NOT NULL,IDT_SORT_INDEX INTEGER(3) NOT NULL,IDT_RCRE_STAFF_ID VARCHAR(20) NOT NULL,IDT_RCRE_TIME DATETIME NOT NULL,IDT_LCHG_STAFF_ID VARCHAR(20),IDT_LCHG_TIME DATETIME,PRIMARY KEY (IDT_TYPE_ID),INDEX WS_IDT_NX1(IDT_RCRE_STAFF_ID),INDEX WS_IDT_NX2(IDT_LCHG_STAFF_ID))";

        private const string SQL_CREATE_TABLE_WS_CUSTOMER = @"CREATE TABLE WS_CUSTOMER (CST_CUSTOMER_ID VARCHAR(32) NOT NULL,CST_CUSTOMER_NAME VARCHAR(20) NOT NULL,CST_SEX INTEGER(1) NOT NULL,CST_BIRTHDAY DATETIME NOT NULL,CST_MOBILE_NUMBER VARCHAR(20) NOT NULL,CST_TELEPHONE VARCHAR(20),CST_EMAIL VARCHAR(30),CST_CONTACT_ADDRESS VARCHAR(80),CST_FOLLOW_STAFF_ID VARCHAR(20),CST_REGISTER_TIME DATETIME NOT NULL,CST_RCRE_STAFF_ID VARCHAR(20) NOT NULL,CST_RCRE_TIME DATETIME NOT NULL,CST_LCHG_STAFF_ID VARCHAR(20),CST_LCHG_TIME DATETIME,PRIMARY KEY (CST_CUSTOMER_ID),INDEX WS_CST_NX1(CST_RCRE_STAFF_ID),INDEX WS_CST_NX2(CST_LCHG_STAFF_ID),INDEX WS_CST_NX3(CST_MOBILE_NUMBER),INDEX WS_CST_NX4(CST_FOLLOW_STAFF_ID),INDEX WS_CST_NX5(CST_CUSTOMER_NAME),INDEX WS_CST_NX6(CST_REGISTER_TIME))";

        private const string SQL_CREATE_TABLE_WS_PASSENGER = @"CREATE TABLE WS_PASSENGER (PSG_CUSTOMER_ID VARCHAR(32) NOT NULL,PSG_PASSENGER_ID VARCHAR(32) NOT NULL,PSG_PASSENGER_NAME VARCHAR(30) NOT NULL,PSG_CHINESE_SPELL VARCHAR(50),PSG_MOBILE_NUMBER VARCHAR(20) NOT NULL,PSG_ID_TYPE_ID VARCHAR(2) NOT NULL,PSG_ID_NUMBER VARCHAR(30) NOT NULL,PSG_EXPIRY_DATE DATETIME NOT NULL,PSG_RCRE_STAFF_ID VARCHAR(20) NOT NULL,PSG_RCRE_TIME DATETIME NOT NULL,PSG_LCHG_STAFF_ID VARCHAR(20),PSG_LCHG_TIME DATETIME,PRIMARY KEY (PSG_PASSENGER_ID),INDEX WS_PSG_NX1(PSG_RCRE_STAFF_ID),INDEX WS_PSG_NX2(PSG_LCHG_STAFF_ID),INDEX WS_PSG_NX3(PSG_ID_TYPE_ID),INDEX WS_PSG_NX4(PSG_CUSTOMER_ID),INDEX WS_PSG_NX5(PSG_PASSENGER_NAME))";

        private const string SQL_CREATE_TABLE_WS_CUSTOMER_REVISIT = @"CREATE TABLE WS_CUSTOMER_REVISIT (CRV_CUSTOMER_ID VARCHAR(32) NOT NULL,CRV_REVISIT_ID VARCHAR(32) NOT NULL,CRV_REVISIT_TIME DATETIME NOT NULL,CRV_STAFF_ID VARCHAR(20) NOT NULL,CRV_REVISIT_REMARK MEDIUMTEXT NOT NULL,CRV_RCRE_STAFF_ID VARCHAR(20) NOT NULL,CRV_RCRE_TIME DATETIME NOT NULL,CRV_LCHG_STAFF_ID VARCHAR(20),CRV_LCHG_TIME DATETIME,PRIMARY KEY (CRV_REVISIT_ID),INDEX WS_CRV_NX1(CRV_RCRE_STAFF_ID),INDEX WS_CRV_NX2(CRV_LCHG_STAFF_ID),INDEX WS_CRV_NX3(CRV_STAFF_ID),INDEX WS_CRV_NX4(CRV_CUSTOMER_ID))";

        private const string SQL_CREATE_TABLE_WS_BOOKING = @"CREATE TABLE WS_BOOKING (BKG_BOOKING_ID VARCHAR(16) NOT NULL,BKG_CUSTOMER_ID VARCHAR(32) NOT NULL,BKG_CUSTOMER_NAME VARCHAR(20) NOT NULL,BKG_MOBILE_NUMBER VARCHAR(20) NOT NULL,BKG_TELEPHONE VARCHAR(20),BKG_EMAIL VARCHAR(30),BKG_BOOKING_TIME DATETIME NOT NULL,BKG_BOOKING_TYPE VARCHAR(3) NOT NULL,BKG_BOOKING_STATUS INTEGER(2) NOT NULL,BKG_PRINT_FLG VARCHAR(1) NOT NULL,BKG_PAID_FLG VARCHAR(1) NOT NULL,BKG_TOTAL_AMOUNT FLOAT(8,2) NOT NULL,BKG_TOTAL_BASE FLOAT(8,2) NOT NULL,BKG_TOTAL_PAID FLOAT(8,2) NOT NULL,BKG_CLAIM_FLG VARCHAR(1) NOT NULL,BKG_CONTACT_ADDRESS VARCHAR(80),BKG_REMARK VARCHAR(80),BKG_SALE_STAFF_ID VARCHAR(20) NOT NULL,BKG_SALE_TIME DATETIME NOT NULL,BKG_PRINT_STAFF_ID VARCHAR(20),BKG_PRINT_TIME DATETIME,BKG_COLLECTION_STAFF_ID VARCHAR(20),BKG_COLLECTION_TIME DATETIME,BKG_RCRE_STAFF_ID VARCHAR(20) NOT NULL,BKG_RCRE_TIME DATETIME NOT NULL,BKG_LCHG_STAFF_ID VARCHAR(20),BKG_LCHG_TIME DATETIME,PRIMARY KEY (BKG_BOOKING_ID),INDEX WS_BKG_NX1(BKG_RCRE_STAFF_ID),INDEX WS_BKG_NX2(BKG_LCHG_STAFF_ID),INDEX WS_BKG_NX3(BKG_CUSTOMER_ID),INDEX WS_BKG_NX4(BKG_SALE_STAFF_ID),INDEX WS_BKG_NX5(BKG_PRINT_STAFF_ID),INDEX WS_BKG_NX6(BKG_COLLECTION_STAFF_ID),INDEX WS_BKG_NX7(BKG_BOOKING_TYPE),INDEX WS_BKG_NX8(BKG_BOOKING_STATUS))";

        private const string SQL_CREATE_TABLE_WS_BOOKING_FLIGHT = @"CREATE TABLE WS_BOOKING_FLIGHT (BKF_BOOKING_ID VARCHAR(16) NOT NULL,BKF_AIRLINE_ID VARCHAR(10) NOT NULL,BKF_FLIGHT_NO VARCHAR(10) NOT NULL,BKF_DEPARTURE_TIME DATETIME NOT NULL,BKF_ARRIVAL_TIME DATETIME NOT NULL,BKF_DEPARTURE_CITY_ID VARCHAR(10) NOT NULL,BKF_ARRIVAL_CITY_ID VARCHAR(10) NOT NULL,INDEX WS_BKF_NX1(BKF_BOOKING_ID),INDEX WS_BKF_NX2(BKF_AIRLINE_ID),INDEX WS_BKF_NX3(BKF_DEPARTURE_CITY_ID),INDEX WS_BKF_NX4(BKF_ARRIVAL_CITY_ID))";

        private const string SQL_CREATE_TABLE_WS_BOOKING_PASSENGER = @"CREATE TABLE WS_BOOKING_PASSENGER (BKP_BOOKING_ID VARCHAR(16) NOT NULL,BKP_PASSENGER_NAME VARCHAR(30) NOT NULL,BKP_CHINESE_SPELL VARCHAR(50),BKP_MOBILE_NUMBER VARCHAR(20) NOT NULL,BKP_ID_TYPE_ID VARCHAR(2) NOT NULL,BKP_ID_NUMBER VARCHAR(30) NOT NULL,BKP_EXPIRY_DATE DATETIME NOT NULL,INDEX WS_BKP_NX1(BKP_BOOKING_ID),INDEX WS_BKP_NX2(BKP_ID_TYPE_ID))";

        private const string SQL_CREATE_TABLE_WS_BOOKING_TICKET = @"CREATE TABLE WS_BOOKING_TICKET (BKT_BOOKING_ID VARCHAR(16) NOT NULL,BKT_TICKET_NO VARCHAR(30) NOT NULL,BKT_AIRLINE_ID VARCHAR(10) NOT NULL,BKT_FLIGHT_NO VARCHAR(10) NOT NULL,BKT_DEPARTURE_TIME DATETIME NOT NULL,BKT_DEPARTURE_CITY_ID VARCHAR(10) NOT NULL,BKT_ARRIVAL_CITY_ID VARCHAR(10) NOT NULL,BKT_TICKET_PRICE FLOAT(8,2),BKT_REFUND_FLG VARCHAR(1) NOT NULL,INDEX WS_BKT_NX1(BKT_BOOKING_ID),INDEX WS_BKT_NX2(BKT_AIRLINE_ID),INDEX WS_BKT_NX3(BKT_DEPARTURE_CITY_ID),INDEX WS_BKT_NX4(BKT_ARRIVAL_CITY_ID))";


        #endregion v1.0

        #region v1.1

        private const string SQL_CREATE_TABLE_WS_NATION = @"CREATE TABLE WS_NATION (NAT_NATION_ID VARCHAR(10) NOT NULL,NAT_NATION_NAME VARCHAR(30) NOT NULL,NAT_SORT_INDEX INTEGER(3) NOT NULL,NAT_RCRE_STAFF_ID VARCHAR(20) NOT NULL,NAT_RCRE_TIME DATETIME NOT NULL,NAT_LCHG_STAFF_ID VARCHAR(20),NAT_LCHG_TIME DATETIME,PRIMARY KEY(NAT_NATION_ID),INDEX WS_NAT_NX1(NAT_RCRE_STAFF_ID),INDEX WS_NAT_NX2(NAT_LCHG_STAFF_ID))";
        private const string SQL_ALTER_TABLE_WS_CITY_NATION_ID = "ALTER TABLE WS_CITY CHANGE COLUMN CIT_PARENT_CITY_ID CIT_NATION_ID varchar(10) NOT NULL, DROP INDEX WS_CIT_NX3, ADD INDEX WS_CIT_NX3(CIT_NATION_ID)";
        private const string SQL_ALTER_TABLE_WS_AIRLINE_NATION_ID = "ALTER TABLE WS_AIRLINE CHANGE COLUMN ALI_AIRLINE_CODE_3 ALI_NATION_ID varchar(10) NOT NULL, ADD INDEX WS_ALI_NX3(ALI_NATION_ID)";
        private const string SQL_ALTER_TABLE_WS_BOOKING_FLIGHT_ID = "ALTER TABLE WS_BOOKING_FLIGHT ADD COLUMN BKF_ID VARCHAR(32) NOT NULL, ADD COLUMN BKF_RCRE_STAFF_ID VARCHAR(20) NOT NULL, ADD COLUMN BKF_RCRE_TIME DATETIME NOT NULL, ADD COLUMN BKF_LCHG_STAFF_ID VARCHAR(20), ADD COLUMN BKF_LCHG_TIME DATETIME, ADD PRIMARY KEY (BKF_ID), ADD INDEX WS_BKF_NX5(BKF_RCRE_STAFF_ID), ADD INDEX WS_BKF_NX6(BKF_LCHG_STAFF_ID)";
        private const string SQL_ALTER_TABLE_WS_BOOKING_PASSENGER_ID = "ALTER TABLE WS_BOOKING_PASSENGER ADD COLUMN BKP_ID VARCHAR(32) NOT NULL, ADD COLUMN BKP_RCRE_STAFF_ID VARCHAR(20) NOT NULL, ADD COLUMN BKP_RCRE_TIME DATETIME NOT NULL, ADD COLUMN BKP_LCHG_STAFF_ID VARCHAR(20), ADD COLUMN BKP_LCHG_TIME DATETIME, ADD PRIMARY KEY (BKP_ID), ADD INDEX WS_BKP_NX3(BKP_RCRE_STAFF_ID), ADD INDEX WS_BKP_NX4(BKP_LCHG_STAFF_ID)";
        private const string SQL_ALTER_TABLE_WS_BOOKING_TICKET_ID = "ALTER TABLE WS_BOOKING_TICKET ADD COLUMN BKT_ID VARCHAR(32) NOT NULL, ADD COLUMN BKT_RCRE_STAFF_ID VARCHAR(20) NOT NULL, ADD COLUMN BKT_RCRE_TIME DATETIME NOT NULL, ADD COLUMN BKT_LCHG_STAFF_ID VARCHAR(20), ADD COLUMN BKT_LCHG_TIME DATETIME, ADD PRIMARY KEY (BKT_ID), ADD INDEX WS_BKT_NX5(BKT_RCRE_STAFF_ID), ADD INDEX WS_BKT_NX6(BKT_LCHG_STAFF_ID)";

        #endregion v1.1

        #region v1.2

        private const string SQL_ALTER_TABLE_WS_BOOKING_REMARK = @"ALTER TABLE WS_BOOKING CHANGE COLUMN BKG_REMARK BKG_REMARK mediumtext DEFAULT NULL";
        private const string SQL_CREATE_TABLE_WS_PRE_REGISTER = @"CREATE TABLE WS_PRE_REGISTER (PRE_REGISTER_ID VARCHAR(32) NOT NULL,PRE_CUSTOMER_ID VARCHAR(32) NOT NULL,PRE_CUSTOMER_NAME VARCHAR(20) NOT NULL, PRE_MOBILE_NUMBER VARCHAR(20) NOT NULL,PRE_TELEPHONE VARCHAR(20),PRE_EMAIL VARCHAR(30),PRE_QQ VARCHAR(30),PRE_MSN VARCHAR(30),PRE_TRAVEL_TYPE INTEGER(1) NOT NULL,PRE_DEPARTURE_CITY_ID VARCHAR(10) NOT NULL,PRE_ARRIVAL_CITY_ID VARCHAR(10) NOT NULL,PRE_AIRLINE_ID VARCHAR(10),PRE_DEPARTURE_DATE DATETIME NOT NULL,PRE_RETURN_DATE DATETIME,PRE_PRINT_DATE DATETIME,PRE_ID_TYPE_ID VARCHAR(2),PRE_ID_NUMBER VARCHAR(30),PRE_EXPIRY_DATE DATETIME,PRE_BIRTHDAY DATETIME,PRE_VISA_TYPE VARCHAR(10) NOT NULL,PRE_TOTAL_PEOPLE INTEGER(2) NOT NULL,PRE_DEAL_DESC VARCHAR(80),PRE_REMARK MEDIUMTEXT,PRE_FOLLOW_STAFF_ID VARCHAR(20),PRE_REGISTER_TIME DATETIME NOT NULL,PRE_TRANSFER_FLG VARCHAR(1) NOT NULL,PRE_BOOKING_ID VARCHAR(16),PRE_BOOKING_TYPE VARCHAR(3) NOT NULL,PRE_RCRE_STAFF_ID VARCHAR(20) NOT NULL,PRE_RCRE_TIME DATETIME NOT NULL,PRE_LCHG_STAFF_ID VARCHAR(20),PRE_LCHG_TIME DATETIME,PRIMARY KEY(PRE_REGISTER_ID),INDEX WS_PRE_NX1(PRE_RCRE_STAFF_ID),INDEX WS_PRE_NX2(PRE_LCHG_STAFF_ID),INDEX WS_PRE_NX3(PRE_MOBILE_NUMBER),INDEX WS_PRE_NX4(PRE_FOLLOW_STAFF_ID),INDEX WS_PRE_NX5(PRE_CUSTOMER_NAME),INDEX WS_PRE_NX6(PRE_REGISTER_TIME),INDEX WS_PRE_NX7(PRE_BOOKING_ID))";

        #endregion v1.2

        #region v1.3

        private const string SQL_ALTER_TABLE_WS_BOOKING_DATA = @"ALTER TABLE WS_BOOKING ADD COLUMN BKG_DATA mediumtext";

        #endregion v1.3

        #region v1.4

        private const string SQL_ALTER_TABLE_WS_PRE_REGISTER = "ALTER TABLE WS_PRE_REGISTER CHANGE COLUMN PRE_MOBILE_NUMBER PRE_MOBILE_NUMBER varchar(20), CHANGE COLUMN PRE_DEPARTURE_CITY_ID PRE_DEPARTURE_CITY_ID varchar(10), CHANGE COLUMN PRE_ARRIVAL_CITY_ID PRE_ARRIVAL_CITY_ID varchar(10), CHANGE COLUMN PRE_DEPARTURE_DATE PRE_DEPARTURE_DATE datetime, ADD COLUMN PRE_WECHAT varchar(20)";
        private const string SQL_ALTER_TABLE_WS_CUSTOMER_MOBILE_NUMBER = "ALTER TABLE WS_CUSTOMER CHANGE COLUMN CST_MOBILE_NUMBER CST_MOBILE_NUMBER varchar(20)";
        private const string SQL_ALTER_TABLE_WS_ROLE_ADD_ROL_FOLLOW_FLG = "ALTER TABLE WS_ROLE ADD COLUMN ROL_FOLLOW_FLG varchar(1)";
        private const string SQL_UPDATE_WS_ROLE_ROL_FOLLOW_FLG = "UPDATE WS_ROLE SET ROL_FOLLOW_FLG='Y' where ROL_FOLLOW_FLG is NULL";

        #endregion v1.4

        #region v1.5

        private const string SQL_ALTER_TABLE_WS_PASSENGER = "ALTER TABLE WS_PASSENGER ADD COLUMN PSG_BIRTHDAY datetime, ADD COLUMN PSG_NATION_ID varchar(10)";
        private const string SQL_ALTER_TABLE_WS_BOOKING_PASSENGER = "ALTER TABLE WS_BOOKING_PASSENGER ADD COLUMN BKP_BIRTHDAY datetime, ADD COLUMN BKP_NATION_ID varchar(10)";
        private const string SQL_UPDATE_WS_PASSENGER_NATION_ID = "UPDATE WS_PASSENGER SET PSG_NATION_ID='CN' where PSG_NATION_ID is NULL";
        private const string SQL_UPDATE_WS_BOOKING_PASSENGER_NATION_ID = "UPDATE WS_BOOKING_PASSENGER SET BKP_NATION_ID='CN' where BKP_NATION_ID is NULL";

        #endregion v1.5

        #region v1.6

        private const string SQL_ALTER_TABLE_WS_PASSENGER_SEX = "ALTER TABLE WS_PASSENGER ADD COLUMN PSG_SEX varchar(1)";
        private const string SQL_ALTER_TABLE_WS_BOOKING_PASSENGER_SEX = "ALTER TABLE WS_BOOKING_PASSENGER ADD COLUMN BKP_SEX varchar(1)";
        private const string SQL_UPDATE_WS_PASSENGER_SEX = "UPDATE WS_PASSENGER SET PSG_SEX='M' where PSG_SEX is NULL";
        private const string SQL_UPDATE_WS_BOOKING_PASSENGER_SEX = "UPDATE WS_BOOKING_PASSENGER SET BKP_SEX='M' where BKP_SEX is NULL";

        #endregion v1.6

        #region v1.7

        private const string SQL_ALTER_TABLE_WS_BOOKING_PRINT_COMPANY_AND_PAYMENT_TYPE = "ALTER TABLE WS_BOOKING ADD COLUMN BKG_PRINT_COMPANY varchar(20), ADD COLUMN BKG_PAYMENT_TYPE varchar(20)";

        #endregion v1.7

        #region v1.8

        //------分界线-------
        // 注释2017.09.04
        //private const string SQL_CREATE_TABLE_WS_HINT = "CREATE TABLE WS_HINT (HIT_HINT_ID varchar(32) NOT NULL,HIT_REGISTER_ID varchar(32) NOT NULL,HIT_HINT_TYPE int(2) NOT NULL,HIT_HINT_DESC mediumtext NOT NULL,HIT_FOLLOW_STAFF_ID varchar(20) DEFAULT NULL,HIT_REGISTER_TIME datetime NOT NULL,HIT_HANDLED_FLG varchar(1) NOT NULL,HIT_RCRE_STAFF_ID varchar(20) NOT NULL,HIT_RCRE_TIME datetime NOT NULL,HIT_LCHG_STAFF_ID varchar(20) DEFAULT NULL,HIT_LCHG_TIME datetime DEFAULT NULL,PRIMARY KEY (HIT_HINT_ID),INDEX WS_PHT_NX1 (PHT_HINT_TYPE),INDEX WS_PHT_NX2 (PHT_REGISTER_ID))";
        //------分界线-------

        #endregion v1.8

        private const string SQL_INSERT_WS_DATABASE_VERSION = "INSERT INTO WS_DATABASE_VERSION(DBV_VERSION,DBV_UPDATE_TIME) VALUES(@Version,@UpdateTime)";
        private const string SQL_COUNT_WS_DATABASE_VERSION = "select count(*) from WS_DATABASE_VERSION where 1=1";
        private const string SQL_SELECT_LAST_VERSION = "select max(DBV_VERSION) from WS_DATABASE_VERSION";

        private const string SQL_WHERE_VERSION = " and DBV_VERSION=@Version ";

        private const string PARM_UPDATE_TIME = "@UpdateTime";
        private const string PARM_VERSION = "@Version";
        private const string PARM_PARAM_KEY = "@ParamKey";
        private const string PARM_PARAM_VALUE = "@ParamValue";
        private const string PARM_FUNCTION_ID = "@FunctionId";
        private const string PARM_FUNCTION_NAME = "@FunctionName";
        private const string PARM_STAFF_ID = "@StaffId";
        private const string PARM_STAFF_NAME = "@StaffName";
        private const string PARM_PASSWORD = "@Password";
        private const string PARM_JOIN_DATE = "@JoinDate";
        private const string PARM_DIMISSION_FLAG = "@DimissionFlag";

        private static DBUpdateDAL instance = new DBUpdateDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static DBUpdateDAL Instance
        {
            get
            {
                return instance;
            }
        }

        private bool IsUpdated(string version)
        {
            try
            {
                return DatabaseVersionDAL.Instance.Exist(version);
            }
            catch
            {
                return false;
            }
        }

        public void UpdateDB()
        {
            this.UpdateDB_1_0();
            this.UpdateDB_1_1();
            this.UpdateDB_1_2();
            this.UpdateDB_1_3();
            this.UpdateDB_1_4();
            this.UpdateDB_1_5();
            this.UpdateDB_1_6();
            this.UpdateDB_1_7();
            this.UpdateDB_1_8();
        }
        string lastVersion;
        public bool IsLastVersion()
        {
            try
            {
                string version = DatabaseVersionDAL.Instance.GetMaxVersion();
                return string.Equals(this.lastVersion, version, StringComparison.OrdinalIgnoreCase);
            }
            catch
            {
                return false;
            }
        }

        #region v1.0

        private List<ModuleModel> GetModules()
        {
            ModuleModel[] ary = new ModuleModel[] {
                new ModuleModel() { Id="01", Name="基础管理", ParentId="00", SortIndex=1 },
                new ModuleModel() { Id="0101", Name="员工管理", ParentId="01", SortIndex=1 },
                new ModuleModel() { Id="0102", Name="角色管理", ParentId="01", SortIndex=2 },
                new ModuleModel() { Id="0103", Name="密码管理", ParentId="01", SortIndex=3 },
                new ModuleModel() { Id="0104", Name="日志管理", ParentId="01", SortIndex=4 },
                new ModuleModel() { Id="0105", Name="城市管理", ParentId="01", SortIndex=5 },
                new ModuleModel() { Id="0106", Name="航空公司管理", ParentId="01", SortIndex=6 },
                new ModuleModel() { Id="0107", Name="证件类型管理", ParentId="01", SortIndex=7 },
                //new ModuleModel() { Id="0108", Name="国家管理", ParentId="01", SortIndex=8 },

                new ModuleModel() { Id="02", Name="客户管理", ParentId="00", SortIndex=2 },
                new ModuleModel() { Id="0201", Name="客户信息管理", ParentId="02", SortIndex=1 },
                new ModuleModel() { Id="0202", Name="乘机人信息管理", ParentId="02", SortIndex=2 },
                new ModuleModel() { Id="0203", Name="回访记录管理", ParentId="02", SortIndex=3 },

                //new ModuleModel() { Id="03", Name="订票管理", ParentId="00", SortIndex=3 },
                //new ModuleModel() { Id="0301", Name="预登记管理", ParentId="03", SortIndex=1 },
                //new ModuleModel() { Id="0302", Name="预订机票管理", ParentId="03", SortIndex=2 },
                //new ModuleModel() { Id="0303", Name="订单监控", ParentId="03", SortIndex=3 },
                //new ModuleModel() { Id="030301", Name="月度销售排行榜", ParentId="0303", SortIndex=1 },
                //new ModuleModel() { Id="030302", Name="周度销售排行榜", ParentId="0303", SortIndex=2 },
                //new ModuleModel() { Id="030303", Name="今天订单情况", ParentId="0303", SortIndex=3 },

                //new ModuleModel() { Id="04", Name="报表管理", ParentId="00", SortIndex=4 },
                //new ModuleModel() { Id="0401", Name="销售报表", ParentId="04", SortIndex=1 },
            };

            List<ModuleModel> modules = new List<ModuleModel>();
            modules.AddRange(ary);

            return modules;
        }
        private List<FunctionModel> GetFunctions()
        {
            FunctionModel[] ary = new FunctionModel[] {
                new FunctionModel() {  Id="010101", Name="查看员工信息", ModuleId="0101", SortIndex=1 },
                new FunctionModel() {  Id="010102", Name="新增员工信息", ModuleId="0101", SortIndex=2 },
                new FunctionModel() {  Id="010103", Name="修改员工信息", ModuleId="0101", SortIndex=3 },
                new FunctionModel() {  Id="010104", Name="删除员工信息", ModuleId="0101", SortIndex=4 },

                new FunctionModel() {  Id="010201", Name="查看角色信息", ModuleId="0102", SortIndex=1 },
                new FunctionModel() {  Id="010202", Name="新增角色信息", ModuleId="0102", SortIndex=2 },
                new FunctionModel() {  Id="010203", Name="修改角色信息", ModuleId="0102", SortIndex=3 },
                new FunctionModel() {  Id="010204", Name="删除角色信息", ModuleId="0102", SortIndex=4 },

                new FunctionModel() {  Id="010303", Name="修改密码", ModuleId="0103", SortIndex=3 },

                new FunctionModel() {  Id="010401", Name="查看日志", ModuleId="0104", SortIndex=1 },

                new FunctionModel() {  Id="010501", Name="查看城市信息", ModuleId="0105", SortIndex=1 },
                new FunctionModel() {  Id="010502", Name="新增城市信息", ModuleId="0105", SortIndex=2 },
                new FunctionModel() {  Id="010503", Name="修改城市信息", ModuleId="0105", SortIndex=3 },
                new FunctionModel() {  Id="010504", Name="删除城市信息", ModuleId="0105", SortIndex=4 },
                //new FunctionModel() {  Id="010505", Name="导入城市信息", ModuleId="0105", SortIndex=5 },

                new FunctionModel() {  Id="010601", Name="查看航空公司信息", ModuleId="0106", SortIndex=1 },
                new FunctionModel() {  Id="010602", Name="新增航空公司信息", ModuleId="0106", SortIndex=2 },
                new FunctionModel() {  Id="010603", Name="修改航空公司信息", ModuleId="0106", SortIndex=3 },
                new FunctionModel() {  Id="010604", Name="删除航空公司信息", ModuleId="0106", SortIndex=4 },
                //new FunctionModel() {  Id="010605", Name="导入航空公司信息", ModuleId="0106", SortIndex=5 },

                new FunctionModel() {  Id="010701", Name="查看证件类型信息", ModuleId="0107", SortIndex=1 },
                new FunctionModel() {  Id="010702", Name="新增证件类型信息", ModuleId="0107", SortIndex=2 },
                new FunctionModel() {  Id="010703", Name="修改证件类型信息", ModuleId="0107", SortIndex=3 },
                new FunctionModel() {  Id="010704", Name="删除证件类型信息", ModuleId="0107", SortIndex=4 },

                //new FunctionModel() {  Id="010801", Name="查看国家信息", ModuleId="0108", SortIndex=1 },
                //new FunctionModel() {  Id="010802", Name="新增国家信息", ModuleId="0108", SortIndex=2 },
                //new FunctionModel() {  Id="010803", Name="修改国家信息", ModuleId="0108", SortIndex=3 },
                //new FunctionModel() {  Id="010804", Name="删除国家信息", ModuleId="0108", SortIndex=4 },
                //new FunctionModel() {  Id="010805", Name="导入国家信息", ModuleId="0108", SortIndex=5 },

                new FunctionModel() {  Id="020101", Name="查看客户信息", ModuleId="0201", SortIndex=1 },
                new FunctionModel() {  Id="020102", Name="新增客户信息", ModuleId="0201", SortIndex=2 },
                new FunctionModel() {  Id="020103", Name="修改客户信息", ModuleId="0201", SortIndex=3 },
                //new FunctionModel() {  Id="020107", Name="允许指派跟进员工", ModuleId="0201", SortIndex=4 },

                new FunctionModel() {  Id="020201", Name="查看乘机人信息", ModuleId="0202", SortIndex=1 },
                new FunctionModel() {  Id="020202", Name="新增乘机人信息", ModuleId="0202", SortIndex=2 },
                new FunctionModel() {  Id="020203", Name="修改乘机人信息", ModuleId="0202", SortIndex=3 },
                new FunctionModel() {  Id="020204", Name="删除乘机人信息", ModuleId="0202", SortIndex=4 },

                new FunctionModel() {  Id="020301", Name="查看回访记录", ModuleId="0203", SortIndex=1 },
                new FunctionModel() {  Id="020302", Name="新增回访记录", ModuleId="0203", SortIndex=2 },

                //new FunctionModel() {  Id="030101", Name="查看预登记信息", ModuleId="0301", SortIndex=1 },
                //new FunctionModel() {  Id="030102", Name="新增预登记信息", ModuleId="0301", SortIndex=2 },
                //new FunctionModel() {  Id="030103", Name="修改预登记信息", ModuleId="0301", SortIndex=3 },

                //new FunctionModel() {  Id="030201", Name="查看预订机票信息", ModuleId="0302", SortIndex=1 },
                //new FunctionModel() {  Id="030202", Name="新增预订机票信息", ModuleId="0302", SortIndex=2 },
                //new FunctionModel() {  Id="030203", Name="修改预订机票信息", ModuleId="0302", SortIndex=3 },
                //new FunctionModel() {  Id="030204", Name="删除预订机票信息", ModuleId="0302", SortIndex=4 },

                //new FunctionModel() {  Id="03030101", Name="查看月度销售排行榜", ModuleId="030301", SortIndex=1 },
                //new FunctionModel() {  Id="03030201", Name="查看周度销售排行榜", ModuleId="030302", SortIndex=1 },
                //new FunctionModel() {  Id="03030301", Name="查看今天订单情况", ModuleId="030303", SortIndex=1 },

                //new FunctionModel() {  Id="040101", Name="员工销售汇总报表", ModuleId="0401", SortIndex=1 },
                //new FunctionModel() {  Id="040102", Name="订单销售情况报表", ModuleId="0401", SortIndex=2 },
            };

            List<FunctionModel> functions = new List<FunctionModel>();
            functions.AddRange(ary);

            return functions;
        }
        private List<RoleModel> GetRoles()
        {
            RoleModel[] ary = new RoleModel[] {
                new RoleModel() { Id="01", Name="系统管理员", Level=0, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
            };

            List<RoleModel> roles = new List<RoleModel>();
            roles.AddRange(ary);

            return roles;
        }
        private List<RoleModuleModel> GetRoleModules()
        {
            List<RoleModuleModel> roleModules = new List<RoleModuleModel>();

            List<ModuleModel> modules = this.GetModules();
            foreach (ModuleModel m in modules)
            {
                roleModules.Add(new RoleModuleModel() { RoleId = "01", ModuleId = m.Id, LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now });
            }

            return roleModules;
        }
        private List<RoleFunctionModel> GetRoleFunctions()
        {
            List<RoleFunctionModel> roleFunctions = new List<RoleFunctionModel>();

            List<FunctionModel> functions = this.GetFunctions();
            foreach (FunctionModel f in functions)
            {
                roleFunctions.Add(new RoleFunctionModel() { RoleId = "01", FunctionId = f.Id, LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now });
            }

            return roleFunctions;
        }
        private List<StaffModel> GetStaffs()
        {
            StaffModel[] ary = new StaffModel[] {
                new StaffModel() { Id="ADMIN", Name="系统管理员", Password=AppLaunch.Common.Security.CryptographyHelper.Instance.MD5(string.Concat("ADMIN","admin")), RoleId="01", JoinDate=DateTime.Now.Date, Dimission=false,  LastModifiedStaffId="WSDBA",  LastModifiedDate=DateTime.Now },
            };

            List<StaffModel> staffs = new List<StaffModel>();
            staffs.AddRange(ary);

            return staffs;
        }
        private List<CityModel> GetCities()
        {
            CityModel[] ary = new CityModel[] {
                new CityModel() { Id="CN", Name="中国", NationId="0", SortIndex=0, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new CityModel() { Id="US", Name="美国", NationId="0", SortIndex=1, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
            };

            List<CityModel> cities = new List<CityModel>();
            cities.AddRange(ary);

            return cities;
        }
        private List<AirlineModel> GetAirlines()
        {
            AirlineModel[] ary = new AirlineModel[] {
                new AirlineModel() { Id="", Name="", EnglishName="", NationId="", SortIndex=0, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
            };

            List<AirlineModel> airlines = new List<AirlineModel>();
            airlines.AddRange(ary);

            return airlines;
        }
        private List<IDTypeModel> GetIDTypes()
        {
            IDTypeModel[] ary = new IDTypeModel[] {
                new IDTypeModel() { Id="01", Name="身份证", Enabled=true, SortIndex=0, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="02", Name="护照", Enabled=true, SortIndex=2, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="03", Name="学生证", Enabled=true, SortIndex=3, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="04", Name="军人证", Enabled=true, SortIndex=4, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="06", Name="驾驶证", Enabled=true, SortIndex=6, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="07", Name="回乡证", Enabled=true, SortIndex=7, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="08", Name="台胞证", Enabled=true, SortIndex=8, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="10", Name="港澳通行证", Enabled=true, SortIndex=10, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="11", Name="国际海员证", Enabled=true, SortIndex=11, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="20", Name="外国人永久居留证", Enabled=true, SortIndex=20, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="21", Name="旅行证", Enabled=true, SortIndex=21, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="22", Name="台湾通行证", Enabled=true, SortIndex=22, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="23", Name="士兵证", Enabled=true, SortIndex=23, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="24", Name="临时身份证", Enabled=true, SortIndex=24, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="25", Name="户口簿", Enabled=true, SortIndex=25, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="26", Name="警官证", Enabled=true, SortIndex=26, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="27", Name="出生证明", Enabled=true, SortIndex=27, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
                new IDTypeModel() { Id="99", Name="其它", Enabled=true, SortIndex=99, LastModifiedStaffId="WSDBA", LastModifiedDate=DateTime.Now },
            };

            List<IDTypeModel> idtypes = new List<IDTypeModel>();
            idtypes.AddRange(ary);

            return idtypes;
        }
        private void UpdateDB_1_0()
        {
            this.lastVersion = "1.0";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_DATABASE_VERSION, null);

                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_GLOBAL_PARAMS, null);

                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_SEQUENCES, null);

                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_MODULE, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_FUNCTION, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_ROLE, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_ROLE_MODULE, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_ROLE_FUNCTION, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_STAFF, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_STAFF_ACTIVITIES, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_STAFF_LOGIN_STATE, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_STAFF_LOG, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_CITY, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_AIRLINE, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_ID_TYPE, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_CUSTOMER, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_PASSENGER, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_CUSTOMER_REVISIT, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_BOOKING, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_BOOKING_FLIGHT, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_BOOKING_PASSENGER, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_BOOKING_TICKET, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Insert(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });
                        SequenceDAL.Instance.Insert(tran, new SequenceModel() { Name = "WS_BKG_ID_SEQ", MinValue = 1, MaxValue = 99999999, NextNumber = 1, IncrementBy = 1, Cycle = true });
                        SequenceDAL.Instance.Insert(tran, new SequenceModel() { Name = "WS_LOG_ID_SEQ", MinValue = 1, MaxValue = 99999999, NextNumber = 1, IncrementBy = 1, Cycle = true });

                        List<ModuleModel> modules = this.GetModules();
                        List<FunctionModel> functions = this.GetFunctions();
                        List<RoleModel> roles = this.GetRoles();
                        List<RoleModuleModel> roleModules = this.GetRoleModules();
                        List<RoleFunctionModel> roleFunctions = this.GetRoleFunctions();
                        List<StaffModel> staffs = this.GetStaffs();
                        List<IDTypeModel> idtypes = this.GetIDTypes();

                        foreach (ModuleModel item in modules)
                        {
                            ModuleDAL.Instance.Insert(tran, item);
                        }
                        foreach (FunctionModel item in functions)
                        {
                            FunctionDAL.Instance.Insert(tran, item);
                        }
                        foreach (RoleModel item in roles)
                        {
                            RoleDAL.Instance.Insert(tran, item);
                        }
                        foreach (RoleModuleModel item in roleModules)
                        {
                            RoleModuleDAL.Instance.Insert(tran, item);
                        }
                        foreach (RoleFunctionModel item in roleFunctions)
                        {
                            RoleFunctionDAL.Instance.Insert(tran, item);
                        }
                        foreach (StaffModel item in staffs)
                        {
                            StaffDAL.Instance.Insert(tran, item);
                        }
                        foreach (IDTypeModel item in idtypes)
                        {
                            IDTypeDAL.Instance.Insert(tran, item);
                        }

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        #endregion v1.0

        #region v1.1
        private void UpdateDB_1_1()
        {
            this.lastVersion = "1.1";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_NATION, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_CITY_NATION_ID, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_AIRLINE_NATION_ID, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_FLIGHT_ID, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_PASSENGER_ID, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_TICKET_ID, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        ModuleModel[] modules = new ModuleModel[] {
                            new ModuleModel() { Id="0108", Name="国家管理", ParentId="01", SortIndex=8 },
                        };
                        FunctionModel[] functions = new FunctionModel[] {
                            new FunctionModel() {  Id="010801", Name="查看国家信息", ModuleId="0101", SortIndex=1 },
                            new FunctionModel() {  Id="010802", Name="新增国家信息", ModuleId="0101", SortIndex=2 },
                            new FunctionModel() {  Id="010803", Name="修改国家信息", ModuleId="0101", SortIndex=3 },
                            new FunctionModel() {  Id="010804", Name="删除国家信息", ModuleId="0101", SortIndex=4 },
                            new FunctionModel() {  Id="010805", Name="导入国家信息", ModuleId="0101", SortIndex=4 },

                            new FunctionModel() {  Id="010505", Name="导入国家信息", ModuleId="0105", SortIndex=4 },
                            new FunctionModel() {  Id="010605", Name="导入航空公司信息", ModuleId="0106", SortIndex=4 },

                            new FunctionModel() {  Id="020107", Name="允许指派跟进员工", ModuleId="0201", SortIndex=4 },
                        };
                        RoleModuleModel[] roleModules = new RoleModuleModel[] {
                            new RoleModuleModel() { RoleId = "01", ModuleId = "0108", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                        };
                        RoleFunctionModel[] roleFunctions = new RoleFunctionModel[] {
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "010801", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "010802", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "010803", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "010804", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "010805", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },

                            new RoleFunctionModel() { RoleId = "01", FunctionId = "010505", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "010605", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },

                            new RoleFunctionModel() { RoleId = "01", FunctionId = "020107", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                        };
                        foreach (ModuleModel item in modules)
                        {
                            ModuleDAL.Instance.Insert(tran, item);
                        }
                        foreach (FunctionModel item in functions)
                        {
                            FunctionDAL.Instance.Insert(tran, item);
                        }
                        foreach (RoleModuleModel item in roleModules)
                        {
                            RoleModuleDAL.Instance.Insert(tran, item);
                        }
                        foreach (RoleFunctionModel item in roleFunctions)
                        {
                            RoleFunctionDAL.Instance.Insert(tran, item);
                        }

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        #endregion v1.1

        #region v1.2
        private void UpdateDB_1_2()
        {
            this.lastVersion = "1.2";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_REMARK, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_PRE_REGISTER, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        ModuleModel[] modules = new ModuleModel[] {
                            new ModuleModel() { Id="03", Name="订票管理", ParentId="00", SortIndex=3 },
                            new ModuleModel() { Id="0301", Name="预登记管理", ParentId="03", SortIndex=1 },
                        };
                        FunctionModel[] functions = new FunctionModel[] {
                            new FunctionModel() {  Id="030101", Name="查看预登记信息", ModuleId="0301", SortIndex=1 },
                            new FunctionModel() {  Id="030102", Name="新增预登记信息", ModuleId="0301", SortIndex=2 },
                            new FunctionModel() {  Id="030103", Name="修改预登记信息", ModuleId="0301", SortIndex=3 },
                        };
                        RoleModuleModel[] roleModules = new RoleModuleModel[] {
                            new RoleModuleModel() { RoleId = "01", ModuleId = "03", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleModuleModel() { RoleId = "01", ModuleId = "0301", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                        };
                        RoleFunctionModel[] roleFunctions = new RoleFunctionModel[] {
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "030101", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "030102", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                            new RoleFunctionModel() { RoleId = "01", FunctionId = "030103", LastModifiedStaffId = "WSDBA", LastModifiedDate = DateTime.Now },
                        };
                        foreach (ModuleModel item in modules)
                        {
                            ModuleDAL.Instance.Insert(tran, item);
                        }
                        foreach (FunctionModel item in functions)
                        {
                            FunctionDAL.Instance.Insert(tran, item);
                        }
                        foreach (RoleModuleModel item in roleModules)
                        {
                            RoleModuleDAL.Instance.Insert(tran, item);
                        }
                        foreach (RoleFunctionModel item in roleFunctions)
                        {
                            RoleFunctionDAL.Instance.Insert(tran, item);
                        }

                        functions = new FunctionModel[] {
                            new FunctionModel() {  Id="010801", Name="查看国家信息", ModuleId="0108", SortIndex=1 },
                            new FunctionModel() {  Id="010802", Name="新增国家信息", ModuleId="0108", SortIndex=2 },
                            new FunctionModel() {  Id="010803", Name="修改国家信息", ModuleId="0108", SortIndex=3 },
                            new FunctionModel() {  Id="010804", Name="删除国家信息", ModuleId="0108", SortIndex=4 },
                            new FunctionModel() {  Id="010805", Name="导入国家信息", ModuleId="0108", SortIndex=5 },

                            new FunctionModel() {  Id="010505", Name="导入城市信息", ModuleId="0105", SortIndex=5 },
                            new FunctionModel() {  Id="010605", Name="导入航空公司信息", ModuleId="0106", SortIndex=5 },
                        };
                        foreach (FunctionModel item in functions)
                        {
                            FunctionDAL.Instance.Update(tran, item);
                        }

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        #endregion v1.2

        #region v1.3

        private void UpdateDB_1_3()
        {
            this.lastVersion = "1.3";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_DATA, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }

            }
        }

        #endregion v1.3

        #region v1.4

        private void UpdateDB_1_4()
        {
            this.lastVersion = "1.4";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_PRE_REGISTER, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_CUSTOMER_MOBILE_NUMBER, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_ROLE_ADD_ROL_FOLLOW_FLG, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_UPDATE_WS_ROLE_ROL_FOLLOW_FLG, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }

            }
        }

        #endregion v1.4

        #region v1.5

        private void UpdateDB_1_5()
        {
            this.lastVersion = "1.5";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_PASSENGER, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_PASSENGER, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_UPDATE_WS_PASSENGER_NATION_ID, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_UPDATE_WS_BOOKING_PASSENGER_NATION_ID, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }

            }
        }

        #endregion v1.5

        #region v1.6

        private void UpdateDB_1_6()
        {
            this.lastVersion = "1.6";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_PASSENGER_SEX, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_PASSENGER_SEX, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_UPDATE_WS_PASSENGER_SEX, null);
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_UPDATE_WS_BOOKING_PASSENGER_SEX, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }

            }
        }

        #endregion v1.6

        #region v1.7

        private void UpdateDB_1_7()
        {
            this.lastVersion = "1.7";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_ALTER_TABLE_WS_BOOKING_PRINT_COMPANY_AND_PAYMENT_TYPE, null);

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }

            }
        }

        #endregion v1.7

        #region v1.8

        private void UpdateDB_1_8()
        {
            this.lastVersion = "1.8";
            if (this.IsUpdated(this.lastVersion)) return;

            using (DbConnection conn = MySQLHelper.Instance.CreateConnection(MySQLHelper.Instance.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        //------分界线-------
                        // 注释2017.09.04
                        //MySQLHelper.Instance.ExecuteNonQuery(tran, CommandType.Text, SQL_CREATE_TABLE_WS_HINT, null);
                        //------分界线-------

                        DatabaseVersionDAL.Instance.Insert(tran, new DatabaseVersionModel() { UpdateTime = DateTime.Now, Version = this.lastVersion });
                        GlobalParamDAL.Instance.Update(tran, new GlobalParamModel() { Key = "VERSION", Value = this.lastVersion });

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }

            }
        }

        #endregion v1.8
    }
}
